package com.ruc.deke.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.stereotype.Component;

import com.ruc.deke.dao.SamplesDAO;
import com.ruc.deke.model.DBRecord;
import com.ruc.deke.utils.ParseUtil;

@Component(value = "samplesDAOImpl")
public class SamplesDAOImpl implements SamplesDAO {

	@Resource(name = "dataSource_rucsamples")
	private DataSource dataSource;

	@Override
	public ArrayList<DBRecord> query(String sql) throws SQLException {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		ArrayList<DBRecord> records = new ArrayList<DBRecord>();
		// System.out.
		System.out.println("get connection .......");
		connection = dataSource.getConnection();
		System.out.println("get connected !");
		statement = connection.createStatement();
		resultSet = statement.executeQuery(sql);
		records = ParseUtil.toDBRecord(resultSet);
		statement.close();
		connection.close();
		// connection.releaseSavepoint(savepoint);
		return records;
	}

	@Override
	public String queryOne(String sql) {
		Statement stmt = null;
		ResultSet rs = null;
		Connection conn = null;
		String returnValue = null;
		try {
			conn = dataSource.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				returnValue = rs.getString(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (SQLException sqlexception) {
				sqlexception.printStackTrace();
			}
		}
		return returnValue;
	}

	@Override
	public ArrayList<DBRecord> query(Connection connection, String sql) throws SQLException {
		Statement statement = null;
		ResultSet resultSet = null;
		ArrayList<DBRecord> records = new ArrayList<DBRecord>();

		statement = connection.createStatement();
		System.out.println(sql);
		//sql="select distinct sno from sc";
		resultSet = statement.executeQuery(sql);
		System.out.println("size:");
		records = ParseUtil.toDBRecord(resultSet);
		System.out.println(records.size());
		statement.close();
		// connection.close();
		// connection.releaseSavepoint(savepoint);

		return records;
	}

	// @Override
	// public ArrayList<DBRecord> query(Connection connection, String sql) {
	// Statement statement =null;
	// ResultSet resultSet = null;
	// ArrayList<DBRecord> records = new ArrayList<DBRecord>();
	//
	// try {
	// statement= connection.createStatement();
	// resultSet = statement.executeQuery(sql);
	// records = ParseUtil.toDBRecord(resultSet);
	// statement.close();
	// } catch (SQLException e) {
	// // TODO Auto-generated catch block
	//// e.printStackTrace();
	// System.out.println("exception:"+e.getMessage());
	// }
	// return records;
	// }

	@Override
	public String queryOne(Connection connection, String sql) {
		Statement stmt = null;
		ResultSet rs = null;
		Connection conn = null;
		String returnValue = null;
		try {
			conn = connection;
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				returnValue = rs.getString(1);
			}
		} catch (Exception e) {
			// e.printStackTrace();
			System.out.println("exception:" + e.getMessage());
		} finally {
			try {
				rs.close();
				stmt.close();
			} catch (SQLException sqlexception) {
				// sqlexception.printStackTrace();
				System.out.println("exception:" + sqlexception.getMessage());
			}
		}
		return returnValue;
	}

}
